// 为现有媒体文件创建PDF转换记录
const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');

// 数据库连接配置
const pool = new Pool({
  host: '118.178.138.0',
  port: 9070,
  database: 'postgres',
  user: 'postgres',
  password: 'difyai123456',
  max: 20,
  idleTimeoutMillis: 90900,
  connectionTimeoutMillis: 2000,
});

async function migrateMediaFilesToPdf() {
  try {
    console.log('🔄 开始为现有媒体文件创建PDF转换记录...\n');
    
    // 查询所有可转换为PDF的媒体文件
    const mediaFilesQuery = `
      SELECT id, user_id, original_name, file_name, file_url, mime_type, file_size
      FROM media_files 
      WHERE mime_type IN (
        'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
        'application/msword',
        'application/vnd.openxmlformats-officedocument.presentationml.presentation',
        'application/vnd.ms-powerpoint',
        'application/pdf'
      )
      ORDER BY created_at DESC
    `;
    
    const mediaFilesResult = await pool.query(mediaFilesQuery);
    console.log(`📁 找到${mediaFilesResult.rows.length}个可处理的文件\n`);
    
    let processedCount = 0;
    let skippedCount = 0;
    
    for (const file of mediaFilesResult.rows) {
      console.log(`处理文件: ${file.original_name} (ID: ${file.id})`);
      
      // 检查是否已有PDF转换记录
      const existingQuery = `
        SELECT id FROM pdf_conversions 
        WHERE original_file_id = $1
      `;
      
      const existingResult = await pool.query(existingQuery, [file.id]);
      
      if (existingResult.rows.length > 0) {
        console.log(`  ⏭️ 已存在PDF转换记录，跳过`);
        skippedCount++;
        continue;
      }
      
      let pdfUrl = '';
      let pdfPath = '';
      let conversionStatus = 'unknown';
      
      // 根据文件类型处理
      if (file.mime_type === 'application/pdf') {
        // PDF文件直接使用原文件
        pdfUrl = file.file_url;
        pdfPath = path.join(process.cwd(), 'uploads', file.file_name);
        conversionStatus = 'completed';
        console.log(`  📄 PDF文件，直接使用原文件`);
      } else {
        // Office文档需要转换
        const nameWithoutExt = path.parse(file.original_name).name;
        const pdfFileName = `${nameWithoutExt}-${Date.now()}-${Math.round(Math.random() * 1000)}.pdf`;
        pdfUrl = `/uploads/${pdfFileName}`;
        pdfPath = path.join(process.cwd(), 'uploads', pdfFileName);
        
        // 检查原文件是否存在
        const originalFilePath = path.join(process.cwd(), 'uploads', file.file_name);
        if (fs.existsSync(originalFilePath)) {
          conversionStatus = 'processing'; // 标记为处理中，等待后续转换
          console.log(`  🔄 Office文档，标记为待转换`);
        } else {
          conversionStatus = 'failed';
          console.log(`  ❌ 原文件不存在，标记为失败`);
        }
      }
      
      // 插入PDF转换记录
      const insertQuery = `
        INSERT INTO pdf_conversions (user_id, original_file_id, pdf_path, pdf_url, conversion_status)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING id
      `;
      
      const insertResult = await pool.query(insertQuery, [
        file.user_id,
        file.id,
        pdfPath,
        pdfUrl,
        conversionStatus
      ]);
      
      console.log(`  ✅ 创建PDF转换记录 (ID: ${insertResult.rows[0].id})`);
      processedCount++;
    }
    
    console.log(`\n📊 处理完成:`);
    console.log(`  - 处理文件数: ${processedCount}`);
    console.log(`  - 跳过文件数: ${skippedCount}`);
    console.log(`  - 总文件数: ${mediaFilesResult.rows.length}`);
    
    // 显示PDF转换记录统计
    const statsQuery = `
      SELECT conversion_status, COUNT(*) as count
      FROM pdf_conversions
      GROUP BY conversion_status
      ORDER BY conversion_status
    `;
    
    const statsResult = await pool.query(statsQuery);
    console.log(`\n📈 PDF转换记录统计:`);
    statsResult.rows.forEach(row => {
      console.log(`  - ${row.conversion_status}: ${row.count}条`);
    });
    
    console.log('\n✅ 迁移完成！');
    
  } catch (error) {
    console.error('❌ 迁移失败:', error);
  } finally {
    await pool.end();
  }
}

// 运行迁移
if (require.main === module) {
  migrateMediaFilesToPdf();
}

module.exports = { migrateMediaFilesToPdf };